﻿using System;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Web;
using System.Web.Mvc;
using GemBox.Spreadsheet;

namespace WebApp.Controllers
{
    public class ExcelFileResult : FileResult
    {
        private readonly string _reportTitle;
        private readonly DataTable _dataTable;
        private readonly int _rowHeight;
        private readonly bool _includeHeader;

        public ExcelFileResult(string reportTitle, DataTable dataTable, string fileName)
            : this(reportTitle, dataTable, fileName, 20, true)
        {
        }

        public ExcelFileResult(string reportTitle, DataTable dataTable, string fileName, int rowHeight, bool includeHeader) : base("application/vnd.ms-excel")
        {
            _reportTitle = reportTitle;
            _dataTable = dataTable;
            _rowHeight = rowHeight;
            _includeHeader = includeHeader;
            FileDownloadName = fileName;
        }

        protected override void WriteFile(HttpResponseBase response)
        {
            var excelFile = new ExcelFile();

            var worksheet = excelFile.Worksheets.Add(FileDownloadName.Replace(".xlsx", string.Empty));

            var firstCell = _includeHeader ? "A5" : "A1";
            var firstRow = _includeHeader ? 4 : 0;

            if (_includeHeader)
            {
                worksheet.Cells[0].Value = "Report Generated: " + DateTime.Now.ToString("g");

                worksheet.Rows[2].Cells[0].Value = _reportTitle;
                worksheet.Rows[2].Cells[0].Style.Font.Weight = ExcelFont.BoldWeight;
                worksheet.Rows[2].Cells[0].Style.Font.Size = 14 * 20;
            }
            
            var options = new InsertDataTableOptions(firstCell);

            worksheet.InsertDataTable(_dataTable ?? new DataTable(), options);

            var numberOfColumns = worksheet.CalculateMaxUsedColumns();

            if (_includeHeader)
            {
                if (numberOfColumns > 4)
                {
                    worksheet.Cells.GetSubrangeAbsolute(0, 0, 0, numberOfColumns - 1).Merged = true;
                    worksheet.Cells.GetSubrangeAbsolute(2, 0, 2, numberOfColumns - 1).Merged = true;
                }
            }

            for (var column = 0; column < numberOfColumns; column++)
            {
                for (var row = firstRow; row < worksheet.Rows.Count; row++)
                {
                    var excelRow = worksheet.Rows[row];
                    excelRow.Height = _rowHeight * 20;
                    var cell = excelRow.Cells[column];

                    cell.Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
                    cell.Style.VerticalAlignment = VerticalAlignmentStyle.Center;
                    cell.Style.Font.Size = 11 * 20;

                    if (row == firstRow)
                    {
                        cell.Style.FillPattern.SetSolid(Color.Black);
                        cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
                        cell.Style.Font.Color = Color.White;
                        cell.Style.Font.Weight = ExcelFont.BoldWeight;
                    }
                    else if (row % 2 == 0)
                    {
                        cell.Style.FillPattern.SetSolid(Color.LightGray);
                    }
                }

                worksheet.Columns[column].AutoFit(1.25f);
            }

            var stream = new MemoryStream();
            excelFile.Save(stream, SaveOptions.XlsxDefault);

            response.BufferOutput = false;
            response.AddHeader("Content-Length", stream.Length.ToString(CultureInfo.InvariantCulture));
            stream.WriteTo(response.OutputStream);
        }
    }
}